{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "find file on path: /Users/jacob/Workspace/aihr\n",
      "find input file:  FL01.2021年3月深圳分公司社保公积金记账表.xlsx\n",
      "all  1 files found!\n",
      "write to file:  社保公积金明细表-2021-05-19-23-43-13.xls\n"
     ]
    }
   ],
   "source": [
    "#!/usr/bin/env python\n",
    "#-*-coding:utf-8-*- \n",
    "\n",
    "import pandas\n",
    "import datetime\n",
    "import os\n",
    "import numpy as np\n",
    "\n",
    "#####################可配置变量##########################################\n",
    "# 使用前请配置\n",
    "# 搜索文件夹路径\n",
    "# path = r'D:/人力资料/' \n",
    "# 搜索文件前缀和后缀\n",
    "input_file_name_prefix = 'FL01.202' #前缀\n",
    "input_file_name_suffix = '社保公积金记账表.xls' #后缀\n",
    "input_file_sheet = '明细表' # 表格页名称\n",
    "# 需要合并数据后写入的目标文件\n",
    "target_file_name  = '社保公积金明细表-2021.xlsx'  #需要读取的文件，在此文件追加行\n",
    "target_file_name_prefix = os.path.splitext(target_file_name)[0] #输出的文件前缀\n",
    "\n",
    "#######################################################################\n",
    "\n",
    "# 全局变量\n",
    "debug_mode = 0\n",
    "fl_file_num = 0\n",
    "\n",
    "target_df = pandas.read_excel(target_file_name)\n",
    "\n",
    "# 检查是否配置搜索制定路径\n",
    "if 'path' in dir():\n",
    "    print('find file on path:', path)\n",
    "else:\n",
    "    path = os.getcwd()\n",
    "    print('find file on path:', path)\n",
    "\n",
    "# 按照文件名称查找文件    \n",
    "for filename in os.listdir(path):\n",
    "    # 检查搜索的文件名\n",
    "    if(filename.rfind(input_file_name_prefix)==0 and filename.rfind(input_file_name_suffix)>0):\n",
    "        # 读取找到的文件\n",
    "        input_file_name = filename\n",
    "        print('find input file: ', input_file_name)\n",
    "        fl_file_num = fl_file_num +1\n",
    "        input_df = pandas.read_excel(input_file_name, input_file_sheet) \n",
    "        input_row_num = input_df.shape[0]\n",
    "        \n",
    "        # 取合计的上一行作为左后一行\n",
    "        end = input_df['机构'].values == '合计'\n",
    "        input_end_row_num = np.flatnonzero(end)\n",
    "        \n",
    "        # 读取合计以上的数据\n",
    "        source_df = input_df.loc[1:input_end_row_num[0]-1,:]\n",
    "\n",
    "        if debug_mode == 1:\n",
    "            print(input_df)\n",
    "            print(input_df.index)\n",
    "            print(input_df.columns)\n",
    "            print(input_row_num)\n",
    "        \n",
    "        # 追加数据\n",
    "        output_df = target_df.append(source_df)\n",
    "        target_df = output_df\n",
    "\n",
    "# 检查读取的总文件夹\n",
    "if fl_file_num > 0:\n",
    "    print('all ', fl_file_num, 'files found!')\n",
    "else:\n",
    "    print('No file found!')       \n",
    "\n",
    "# 写入文件，去除首列序号    \n",
    "output_file_name = target_file_name_prefix + datetime.datetime.now().strftime(\"-%m-%d-%H-%M-%S\")+'.xls'\n",
    "print('write to file: ', output_file_name)\n",
    "output_df.to_excel(output_file_name, sheet_name='2021', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
